package com.poobo.dao.impl;

import java.io.File;
import java.io.FileInputStream;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;


import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Repository;


import com.poobo.base.impl.BaseDaoImpl;
import com.poobo.dao.IEquipmentDao;
import com.poobo.entity.TbEqGamesInfo;
import com.poobo.entity.TbEquipment;
import com.poobo.util.date.DateUtil;
import com.poobo.util.pager.Page;
import com.poobo.util.str.StrUtil;
@Repository
public class EquipmentDaoImpl extends BaseDaoImpl implements IEquipmentDao{

	@Override
	public TbEquipment queryEquipmentById(Integer id) {
		return selectObjectById(TbEquipment.class, id);
	}

	@Override
	public Page queryEquipmentPage(TbEquipment equipment , Page page) {
		String hql = " from TbEquipment where 1=1 ";
		String countHql = " select count(*) from TbEquipment where 1=1 ";
		List<Object> params = new ArrayList<Object>();
		String orderBy = " order by status desc,id desc ";
		StringBuffer where = new StringBuffer();
		if( equipment != null ){
			if( !StrUtil.isEmpty(equipment.getNumber()) && equipment.getNumber() != "" ){
				where.append(" and number like ? ");
				params.add("%" + equipment.getNumber() + "%");
			}
			if( !StrUtil.isEmpty(equipment.getShopId()) && equipment.getShopId() != "" ){
				where.append(" and shopId like ? ");
				params.add("%" + equipment.getShopId() + "%");
			}
			if( equipment.getStatus() != null ){
				where.append(" and status = ? ");
				params.add(equipment.getStatus());
			}
		}
		page.setBaseHql(hql + where.toString() + orderBy);
		page.setCountHql(countHql + where.toString());
		page.setParam(params);
		return getPageByHql(page);
	}
	
	@Override
	public void deleteEquipmentById(Integer id) {
		String hql = " delete from TbEquipment where id = "+ id +" ";
		delObjectByIds(hql);
	}

	@Override
	public void saveEquipment(TbEquipment equipment) {
		saveOrUpdate(equipment);
	}

	@Override
	public void saveEquipmentExcel( List<TbEquipment> equipments, File file ) {
		try {
			String fileType = file.getName().substring(file.getName().lastIndexOf(".")+1);
			//创建工作文档对象   
	        if (fileType.equals("xls")) {  
	        	Workbook book = Workbook.getWorkbook( file );
	        	// 获得第一个工作表对象
				Sheet sheet = book.getSheet( 0 );
				for(int i = 2 ; i < sheet.getRows() ; i++ ){//从第3行开始取值
					TbEquipment equipment = new TbEquipment();
					for( int j = 0; j < 3 ; j++ ){//限定只取前3列
						Cell cell = sheet.getCell( j , i );//前一个参数代表列，后一个参数代表行
						String result = cell.getContents();
						if( j == 0 ){
							equipment.setNumber(result);
						}else if( j == 1 ){
							equipment.setShopId(result);
						}else{
							equipment.setAddress(result);
						}
						System.out.print("  "+result);
					}
					equipment.setStatus((short)1);
					equipment.setCreateDate( new Timestamp( System.currentTimeMillis()) );
					Integer flag = 0;
					if( equipments != null && equipments.size()>0 ){
						for( int k=0; k<equipments.size(); k++ ){
							if( equipments.get(k).getNumber().equals(equipment.getNumber())){
								flag = 1;//数据库中已存在
							}
						}
					}
					if( flag==0 ){						
						saveOrUpdate(equipment);
					}
					System.out.println();
				}
				book.close();
	        }else if(fileType.equals("xlsx")) {  
	        	FileInputStream fis = new FileInputStream(file.getAbsolutePath());
	        	// 构造 XSSFWorkbook 对象， 传入文件路径或文件字节流
	        	XSSFWorkbook xwb = new XSSFWorkbook(fis);
	        	//XSSFWorkbook xwb = new XSSFWorkbook(file.getAbsolutePath());  
	        	// 读取第一张表格内容  
	        	XSSFSheet sheet = xwb.getSheetAt(0);  
	        	// 定义 row  
	        	XSSFRow row = null;  
	        	// 循环输出表格中的内容  
	        	for (int i = sheet.getFirstRowNum()+2; i < sheet.getPhysicalNumberOfRows(); i++) {  
	        		TbEquipment equipment = new TbEquipment();
	        		row = sheet.getRow(i);  
	        	    for (int j = row.getFirstCellNum(); j < 3; j++) {  //row.getPhysicalNumberOfCells()
	        	    	String result = null;
	        	    	result = row.getCell(j).toString(); 
	        	    	if( j == 0 ){
							equipment.setNumber(result);
						}else if( j == 1 ){
							equipment.setShopId(result);
						}else{
							equipment.setAddress(result);
						}
	        	        System.out.print(result + "\t");  
	        	    }  
	        	    equipment.setStatus((short)1);
					equipment.setCreateDate( new Timestamp( System.currentTimeMillis()) );
					Integer flag = 0;
					if( equipments != null && equipments.size()>0 ){
						for( int k=0; k<equipments.size(); k++ ){
							if( equipments.get(k).getNumber().equals(equipment.getNumber())){
								equipments.get(k).setAddress(equipment.getAddress());
								equipments.get(k).setShopId(equipment.getShopId());
								equipments.get(k).setCreateDate(DateUtil.getNowTime());
								saveOrUpdate(equipments.get(k));
								flag = 1;//数据库中已存在
							}
						}
					}
					if( flag==0 ){						
						saveOrUpdate(equipment);
					}
	        	    System.out.println("");  
	        	} 
	        	fis.close();
	        }  
	        else{  
	            System.out.println("您的文档格式不正确！");  
	        } 
		}catch (Exception e) {
			e.printStackTrace();
		}
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<TbEquipment> queryAll() {
		String hql = " from TbEquipment ";
		return (List<TbEquipment>) findAll(hql);
	}

	@Override
	public List<TbEquipment> querySome() {
		String sql = " select tt.* from" +
				"(select a.ID,a.createDate,a.address,a.`name`,a.`status`,a.number,a.shopId,b.eId " +
				"from tb_equipment a left join tb_equipment_configlist b on a.ID = b.eId group by a.ID) " +
				"as tt where ISNULL(tt.eId) and tt.`status`=1; ";
		List<Map<Object,Object>> list = getMapBySql(sql, null);
		List<TbEquipment> equipments = new ArrayList<TbEquipment>();
		for( int i=0; i<list.size(); i++ ){
			TbEquipment equipment = new TbEquipment();
			Integer id = Integer.parseInt(list.get(i).get("ID").toString());
			if( id != null){			
				equipment.setId(id);
				equipment.setNumber((String)list.get(i).get("number"));
				equipment.setCreateDate((Timestamp)list.get(i).get("createDate"));
				equipment.setAddress((String)list.get(i).get("address"));
				equipment.setShopId((String)list.get(i).get("shopId"));
			}
			equipments.add(equipment);
		}
		return equipments;
	}

	@Override
	public TbEquipment queryEquipmentByNumber(String number) {
		String hql = " from TbEquipment where number='"+number+"' and status=1 ";
		log.info("hql="+hql);
		@SuppressWarnings("unchecked")
		List<TbEquipment> list = (List<TbEquipment>)findAll(hql);
		if( !list.isEmpty() && list.size() > 0 ){
			log.info("设备Id="+list.get(0).getId());
			return list.get(0);
		}else {			
			return null;
		}
	}

	@Override
	public TbEquipment queryEquipmentByNumber2(String number) {
		String hql = " from TbEquipment where number='"+number+"' ";
		@SuppressWarnings("unchecked")
		List<TbEquipment> list = (List<TbEquipment>)findAll(hql);
		if( !list.isEmpty() && list.size() > 0 ){
			return list.get(0);
		}else {			
			return null;
		}
	}

	@Override
	public List<TbEquipment> queryNotConfigGameEqupments() {
		String hql = "from TbEqGamesInfo";
		List<TbEquipment> returnList = new ArrayList<TbEquipment>();
		@SuppressWarnings("unchecked")
		List<TbEqGamesInfo> eqGamesInfos = (List<TbEqGamesInfo>)findAll(hql);
		List<TbEquipment> allEquipments = queryAll(); 
		if(allEquipments!=null && allEquipments.size()>0 && eqGamesInfos!=null && eqGamesInfos.size()>0){
			for(int i=0; i<allEquipments.size(); i++){
				int index = 0;
				for(int j=0; j<eqGamesInfos.size(); j++){
					if (allEquipments.get(i).getId().equals(eqGamesInfos.get(j).getEqid())) {
						index++;
					}
				}
				if( index == 0 ){					
					returnList.add(allEquipments.get(i));
				}
			}
		}else {
			returnList = allEquipments;
		}
		return returnList;
	}
}
